#include "DataBase.h"

DataBase::DataBase() {
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("114.132.169.5");
    db.setPort(3306);
    db.setDatabaseName("test1");
    db.setUserName("root");
    db.setPassword("lbhxksY12");
    if (!db.open()) {
        QMessageBox::warning(this, "错误提示","无法连接数据库");
    }
}

DataBase::DataBase(QString name,QString pwd){
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("114.132.169.5");
    db.setPort(3306);
    db.setDatabaseName("test1");
    db.setUserName(name);
    db.setPassword(pwd);
    if (!db.open()) {
        QMessageBox::warning(this, "错误提示","无法连接数据库");
    }
}

QString DataBase::getCurrentTime() {
    QDateTime currentDateTime = QDateTime::currentDateTime();
    return currentDateTime.toString("yyyy-MM-dd");
}

int DataBase::loginCheck(QString username, QString password) {
    QSqlQuery query(db);
    QString sql = "SELECT c_password FROM cards where c_cardid = :username";

    // 准备查询
    query.prepare(sql);
    query.bindValue(":username", username);

    // 执行查询
    if (query.exec()) {
        if (query.next()) {
            QString storedPassword = query.value(0).toString();
            if (storedPassword == password) {
                return 1; // 登录成功
            }
        }
    }

    return 0; // 登录失败
}

void DataBase::libInsert(QString b_isbn,QString b_cate,QString b_author,QString b_title){
    QSqlQuery query(db);
    query.prepare("CALL InsertCategory(:value1)"); // 注意这里调用存储过程
    query.bindValue(":value1", b_cate);
    if(query.exec()){
        query.prepare("INSERT INTO books (b_isbn, b_categoryid, b_author,b_title,b_state) VALUES (:value1, :value2, :value3,:value4,'1')");
        query.bindValue(":value1", b_isbn);
        query.bindValue(":value2", b_cate);
        query.bindValue(":value3", b_author);
        query.bindValue(":value4", b_title);
        if (!query.exec()) {
            QMessageBox::warning(this, "错误提示","图书基本信息表插入数据失败");
        }else{
            QMessageBox::information(this, "提示", "图书基本信息表插入数据成功");
        }
    }else{
        QMessageBox::information(this, "提示", "该分类编号已存在");
        query.prepare("INSERT INTO books (b_isbn, b_categoryid, b_author,b_title,b_state) VALUES (:value1, :value2, :value3,:value4,'1')");
        query.bindValue(":value1", b_isbn);
        query.bindValue(":value2", b_cate);
        query.bindValue(":value3", b_author);
        query.bindValue(":value4", b_title);
        if (!query.exec()) {
            QMessageBox::warning(this, "错误提示","图书基本信息表插入数据失败");
        }else{
            QMessageBox::information(this, "提示", "图书基本信息表插入数据成功");
        }
    }

}

void DataBase::libUpdate(QString b_isbn,QString title,QString name,QString category){
    QSqlQuery query(db);
    query.prepare("update books set b_title=:value1,b_author=:value3,b_category=:value4 where b_isbn=:value2");
    query.bindValue(":value1", title);
    query.bindValue(":value2", b_isbn);
    query.bindValue(":value3", name);
    query.bindValue(":value2", category);
    if(query.exec()){
        QMessageBox::information(this, "提示", "图书基本信息表更新成功");
    }else{
        QMessageBox::warning(this, "错误提示","图书基本信息表更新失败");
    }

}

void DataBase::libDelete(QString b_isbn){
    QSqlQuery query(db);
    query.prepare("call DeleteBook(:value)");
    query.bindValue(":value", b_isbn);

    if (!query.exec()) {
        QMessageBox::warning(this, "错误提示","图书基本信息表数据删除失败");
    } else {
        QMessageBox::information(this, "提示", "图书基本信息表数据删除成功");
    }
}

void DataBase::bookBorrow(QString b_cardid,QString b_isbn){
    QSqlQuery query(db);
    query.prepare("select * from books WHERE b_isbn = :condition and b_state=1");
    query.bindValue(":condition", b_isbn);

    if (query.exec()&& query.next()) {
        QString date = getCurrentTime();
        borrowInsert(date,b_cardid,b_isbn);
    } else {
        QMessageBox::warning(this, "错误提示","此书已被借走或暂无此书");
    }
}

void DataBase::borrowInsert(QString b_date,QString b_cardid,QString b_isbn){
    QSqlQuery query(db);
    query.prepare("INSERT INTO borrow (b_borrowdate, b_cardid, b_isbn) VALUES (:value1, :value2, :value3)");
    query.bindValue(":value1", b_date);
    query.bindValue(":value2", b_cardid);
    query.bindValue(":value3", b_isbn);
    if(query.exec()){
        query.prepare("update books set b_state=0 WHERE b_isbn = :condition and b_state=1");
        query.bindValue(":condition", b_isbn);
        query.exec();
        int num=BorrowCountPro(b_cardid);
        QMessageBox::information(this, "提示", "借书成功,这是您借阅的第"+QString::number(num)+"本书");
    }else{
        QMessageBox::warning(this, "错误提示","借阅失败，请联系管理员");
        qDebug() << "Error in borrowInsert insert query:" << query.lastError().text();
    }
}

void DataBase::borrowUpdate(QString b_cardid,QString b_isbn){
    QSqlQuery query(db);
    query.prepare("CALL DeleteBorrow(:cardid, :isbn)"); // 注意这里调用存储过程
    query.bindValue(":cardid", b_cardid);
    query.bindValue(":isbn", b_isbn);
    if (!query.exec()) {
        QMessageBox::warning(this, "错误提示2","还书失败(借阅信息表)");
    }else{
        QMessageBox::information(this, "提示", "还书成功");
    }
}

void DataBase::returnBook(QString b_cardid,QString b_isbn){
    QSqlQuery query(db);
    query.prepare("select b_date from borrow where b_cardid =:value1 and b_isbn =:value2 and b_state=NULL");
    query.bindValue(":value1", b_cardid);
    query.bindValue(":value2", b_isbn);
    QDateTime old_dateTime;
    QString date = getCurrentTime();
    if (query.exec()) {
        while (query.next()) {
            old_dateTime = query.value(0).toDateTime();
        }
        QDateTime dateTime = QDateTime::fromString(date, "yyyy-MM-dd");
        int daysDifference = old_dateTime.daysTo(dateTime);
        if (daysDifference>1){
            query.prepare("update borrow set b_state ='逾期' where b_cardid =:value1 and b_isbn =:value2");
            query.bindValue(":value1", b_cardid);
            query.exec();
            QMessageBox::warning(this, "警告提示","还书逾期，请联系管理员还书");
        }else{
            query.prepare("update books set b_state =1 where  b_isbn =:value1");
            query.bindValue(":value1", b_isbn);
            if(query.exec()){
                borrowUpdate(b_cardid,b_isbn);
            }else{
                QMessageBox::warning(this, "错误提示1","还书失败(图书基本信息表)");
            }
        }
    }else{
        QMessageBox::warning(this, "错误提示","该用户未借此书或借书已逾期（逾期请联系管理员）");
    }

}

void DataBase::overDue(QString b_cardid,QString b_isbn){
    QSqlQuery query(db);
    query.prepare("update books set b_state =1 where  b_isbn =:value1");
    query.bindValue(":value1", b_isbn);
    if(query.exec()){
        borrowUpdate(b_cardid,b_isbn);
    }else{
        QMessageBox::warning(this, "错误提示1", "还书失败（图书基本信息表）");
    }
}

void DataBase::cardHandle(QString name,QString cardid,QString password,QString phone){
    QSqlQuery query(db);
    query.prepare("select * from cards where c_cardid =:value1");
    query.bindValue(":value1",cardid);
    if(query.exec()&& query.next()){
        QMessageBox::warning(this, "错误提示", "该卡号已存在，请重新选择卡号");
    }else{
        query.prepare("call insertCard(:value1,:value2,:value3,:value4)");
        query.bindValue(":value1",name);
        query.bindValue(":value2",cardid);
        query.bindValue(":value3",password);
        query.bindValue(":value4",phone);
        if(query.exec()){
            int num = CardCount(); // 假设 CardCount() 返回的是一个整数
            QString numString = QString::number(num); // 将整数转换为字符串
            QMessageBox::information(this, "提示", "借记卡办理成功,您是第" + numString + "位用户");
        }else{
            QMessageBox::warning(this, "错误提示", "借记卡办理失败，请联系管理员");
        }
    }
}

QVector<QVector<QString>> DataBase::libQuery(QString name, QString cate,QString isbn,QString author) {
    QSqlQuery query(db);  //通过数据库创建对象
    QVector<QVector<QString>> data; // 创建一个可变的二维数组
    QString sql = "SELECT * "
                  "FROM query "
                  "WHERE b_title LIKE '%" + name + "%' "
                           "AND b_isbn LIKE '%" + isbn +"%' "
                           "AND b_author LIKE '%" + author +"%' "
                             "AND b_categoryid IN "
                             "(SELECT ca_categoryid "
                             "FROM categories "
                             "WHERE ca_categoryname LIKE '%" + cate + "%');";
    query.prepare(sql); //执行语句
    if (!query.exec()) {
        QMessageBox::information(this, "infor", "执行出错");
        return data;
    }
    int columnCount = query.record().count(); // 获取查询结果的列数
    while (query.next()) {
        QVector<QString> row1;
        for (int i = 0; i < columnCount; i++) {
            QString value = query.value(i).toString();//将query第一行的数据的每一列分别转化成字符串
            row1.append(value); // 将每一列的值添加到 rowData 中
        }
        data.append(row1);
    }
    return data;
}

void DataBase::dateBackups() {
    QStringList args;
    args << "--add-drop-table" << "-h" << "114.132.169.5" << "-u" << "root" << "-plbhxksY12"  << "test1";

    QProcess *process = new QProcess;
    process->setEnvironment(QProcess::systemEnvironment());
    process->setStandardOutputFile("d:/backup.sql"); // 指定文件路径

    process->start("mysqldump", args);

    if (process->waitForFinished()) {
        if (process->exitStatus() == QProcess::NormalExit) {
            int exitCode = process->exitCode();
            if (exitCode == 0) {
                QMessageBox::information(this, "提示", "数据库数据备份成功");
            } else {
                QMessageBox::warning(this, "错误提示", "备份过程出错，错误码: " + QString::number(exitCode));
            }
        } else {
            QMessageBox::warning(this, "错误提示", "备份过程异常退出，退出状态: " + QString(process->exitStatus() == QProcess::CrashExit ? "CrashExit" : "NormalExit"));
        }
    } else {
        QMessageBox::warning(this, "错误提示", "无法结束数据库备份进程");
    }

}

void DataBase::restoreDatabase()
{
    QStringList arguments;
    arguments << "--host=114.132.169.5" << "--user=root" << "--password=lbhxksY12" << "test1";

    QProcess *process = new QProcess;
    process->setEnvironment(QProcess::systemEnvironment());

    QFile backupFile("D:/backup.sql");
    if (!backupFile.open(QIODevice::ReadOnly)) {
        QMessageBox::warning(this, "错误提示", "数据库备份数据未找到，请先备份");
        return;
    }

    process->setStandardInputFile("D:/backup.sql"); // 指定备份文件路径

    process->start("mysql", arguments);

    if (process->waitForFinished()) {
        if (process->exitStatus() == QProcess::NormalExit) {
            int exitCode = process->exitCode();
            if (exitCode == 0) {
                QMessageBox::information(this, "提示", "数据库数据还原成功");
            } else {
                QMessageBox::warning(this, "错误提示", "还原过程出错，错误码: " + QString::number(exitCode));
            }
        } else {
            QMessageBox::warning(this, "错误提示", "还原过程异常退出，退出状态: " + QString(process->exitStatus() == QProcess::CrashExit ? "CrashExit" : "NormalExit"));
        }
    } else {
        QMessageBox::warning(this, "错误提示", "无法结束数据库还原进程");
    }
}

int DataBase::CategoryCount(){
    QSqlQuery query(db);
    query.prepare("SELECT CategoryCount()");

    if (!query.exec()) {
        QMessageBox::warning(this, "错误提示", "分类信息表查询失败");
        return -1;
    }else{
        if (query.next()) {
            return query.value(0).toInt(); // 假设查询返回的是一个整数
        } else {
            return 0; // 或者根据实际情况返回适当的默认值
        }
    }
}

int DataBase::BooksCount(){
    QSqlQuery query(db);
    query.prepare("SELECT BooksCount()");

    if (!query.exec()) {
        QMessageBox::warning(this, "错误提示", "图书基本信息表查询失败");
        return -1;
    }else{
        if (query.next()) {
            return query.value(0).toInt(); // 假设查询返回的是一个整数
        } else {
            return 0; // 或者根据实际情况返回适当的默认值
        }
    }
}

int DataBase::CardCount(){
    QSqlQuery query(db);
    query.prepare("SELECT CardCount()");

    if (!query.exec()) {
        QMessageBox::warning(this, "错误提示", "借记卡表查询失败");
        return -1;
    }else{
        if (query.next()) {
            return query.value(0).toInt(); // 假设查询返回的是一个整数
        } else {
            return 0; // 或者根据实际情况返回适当的默认值
        }
    }
}

int DataBase::BorrowCount(){
    QSqlQuery query(db);
    query.prepare("SELECT BorrowCount()");

    if (!query.exec()) {
        QMessageBox::warning(this, "错误提示", "借阅信息表表查询失败");
        return -1;
    }else{
        if (query.next()) {
            return query.value(0).toInt(); // 假设查询返回的是一个整数
        } else {
            return 0; // 或者根据实际情况返回适当的默认值
        }
    }
}

int DataBase::BorrowCountPro(QString cardid){
    QSqlQuery query(db);
    query.prepare("SELECT BorrowCountPro(:value)");
    query.bindValue(":value",cardid);

    if (!query.exec()) {
        QMessageBox::warning(this, "错误提示", "借记卡表查询失败");
        return -1;
    }else{
        if (query.next()) {
            return query.value(0).toInt(); // 假设查询返回的是一个整数
        } else {
            return 0; // 或者根据实际情况返回适当的默认值
        }
    }
}
